import os
import sqlite3
import  time ,lib
import chardet
from datetime import datetime
from lib import config
from diskcache import Cache

CachePath = os.path.abspath('.') + "\\data"

class Material:
    def __init__(self):
        # self.database_name = config.rootPath+"\\data\\ESONSEO.DAT"
        # self.database_name = "E:/case2024/playwright_baidu_sendpacke/data/material.dat"
        self.database_name =  config.rootPath+"\\data\\material.dat"
        # self.username = 'SYSDBA'
        # self.password = 'eade61eddatabase2'
        # # self.password = 'passwd'
        try:
            # conn = fdb.connect(dsn=self.database_name,user=self.username,password=self.password)
            conn = sqlite3.connect(self.database_name)
            cur = conn.cursor()
            cur.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='kp_site'")
            table_exists = cur.fetchone() is not None
            if not table_exists:
                self.createTbSite()
                self.createTbVariables()
            cur.close()
            conn.close()

        except sqlite3.Error as e:
            # 打印其他错误信息
            print(f"创建表时出错: {e}")

 
    def createTbSite(self):
        conn = sqlite3.connect(self.database_name)
        cur = conn.cursor()
        table_name = "kp_site"
        # 判断 kp_material 表是否存在
        # 如果表不存在，则创建表
        # , ranking 排名
        # workstate  1表示已经选择在工作，0表示未。
        cur.execute(f'''
                    CREATE TABLE {table_name} (
                            id INTEGER PRIMARY KEY AUTOINCREMENT,
                            seourl VARCHAR(255),
                            web_name VARCHAR(255),
                            ranking INTEGER,
                            workstate INTEGER default 0,
                            seokeyword varchar (255),                     
                            isuse INTEGER default 1,
                            clicktotal INTEGER default 0,
                            successtotal INTEGER default 0,
                            create_fmt VARCHAR(30),
                            lasttime_fmt VARCHAR(30),
                            remark VARCHAR(255)                               
                        )
                        ''')
        conn.commit()
        cur.close()
        conn.close()


    def createTbVariables(self):
        conn = sqlite3.connect(self.database_name)
        cur = conn.cursor()

        table_name = 'kp_variables'
        cur.execute(f"""
            CREATE TABLE {table_name}
            ( id INTEGER PRIMARY KEY AUTOINCREMENT,
            category VARCHAR(200) ,
            key VARCHAR(200) ,
            variables VARCHAR(255) );
        """)
        conn.commit()

        dbconf = {}
        dbconf['search_stay_min_num'] = 2
        dbconf['search_stay_max_num'] = 10
        dbconf['depth_max'] = 10
        dbconf['threads_max_num'] = 2
        dbconf['memory_min_num'] = 1024
        dbconf['proxy_url'] = ''
        dbconf['page_load_timeout'] = 30
        dbconf['proxy_ip_interval'] = 5
        category = 'bdconfig'
        # 构建要插入的元组列表
        insert_values = [
            (key, dbconf[key], category)
            for key in dbconf
        ]
        cur.executemany(
            f"INSERT INTO {table_name} (key,variables,category) VALUES (?,?,?)",
            insert_values
        )
        conn.commit()

        dbconf = {}
        now = datetime.now()
        dbconf['first_start'] = lib.helpers.encryptAppcodekey(now.strftime("%Y-%m-%d"))
        dbconf['disclaimer_agree'] = False
        dbconf['category'] = 'app'
        insert_values2 = [
            (key, dbconf[key], category)
            for key in dbconf
        ]
        cur.executemany(
            f"INSERT INTO {table_name} (key,variables,category) VALUES (?,?,?)",
            insert_values2
        )
        conn.commit()
        cur.close()
        conn.close()

    def getBdConfigs(self):
        conn = sqlite3.connect(self.database_name)
        cur = conn.cursor()
        table_name = "kp_variables"
        category = 'bdconfig'
        cursor = cur.execute(f"select * from {table_name} where category='{category}'")
        fetchRets = cursor.fetchall()
        conn.commit()
        cur.close()
        conn.close()
        dbconf = {}
        for r in fetchRets:
            dbconf[r[2]] = r[3]
        return dbconf


    def updateTbVariables(self,data):
        conn = sqlite3.connect(self.database_name)
        cur = conn.cursor()
        table_name = "kp_variables"
        cursor = cur.execute(f"select * from {table_name}  where `key`=\"{data['key']}\"")
        fetchRet = cursor.fetchone()
        if fetchRet is None:
            cur.execute(
                f"INSERT INTO {table_name} (key,variables,category) VALUES (?,?,?)",
                (data['key'], data['variables'],data['category']))
        else:
            cur.execute(
                f"UPDATE {table_name} SET variables=\"{data['variables']}\"  WHERE key = \"{data['key']}\"" )

        cur.commit()
        conn.commit()
        conn.close()
        reqdata = {}
        reqdata['status'] = 'success'
        reqdata['message'] = '新增成功!'
        return self.outJson(reqdata)

    def getTbVariables(self,key):
        table_name = "kp_variables"
        conn = sqlite3.connect(self.database_name)
        cur = conn.cursor()
        cursor = cur.execute(f"select * from {table_name} where key='{key}'")
        fetchRet = cursor.fetchone()
        if fetchRet is None:
            reqdata = {}
            reqdata['code'] = -1000
            reqdata['message'] = '操作失败!'
            reqdata['data'] = fetchRet
            
        else:
            reqdata = {}
            reqdata['code'] = 0
            reqdata['data'] = fetchRet
            reqdata['message'] = '新增成功!'

        response_dict = {
            "code": reqdata['code'],
            "message": reqdata['message'],
            "requestdata": reqdata['data']
        }
        
        return response_dict


    def getTbVariablesF(self,key):
        table_name = "kp_variables"
        conn = sqlite3.connect(self.database_name)
        cur = conn.cursor()
        cursor = cur.execute(f"select * from {table_name} where key='{key}'")
        fetchRet = cursor.fetchone()
        if fetchRet is None:
            reqdata = {}
            reqdata['status'] = 'error'
            reqdata['message'] = '操作失败!'
            reqdata['data'] = fetchRet
            return self.outJson(reqdata)
        else:
            reqdata = {}
            reqdata['status'] = 'success'
            reqdata['data'] =  fetchRet
            reqdata['message'] = '新增成功!'
            return self.outJson(reqdata)


    def delete_all_data(self):
        table_name = 'kp_site'
        conn = sqlite3.connect(self.database_name)
        cur = conn.cursor()
        cur.execute(f'DELETE FROM {table_name}')
        conn.commit()
        conn.close()
        data = {}
        data['status'] = 'success'
        data['message'] = '成功删除！'
        return self.outJson(data)

    def updateRec(self,data):
        table_name = 'kp_site'
        conn = sqlite3.connect(self.database_name)
        cur = conn.cursor()
        cur.execute(f"UPDATE {table_name} SET web_name=?, seourl=?,seokeyword=?,remark=?, isuse=?  WHERE id = ?", (data['web_name'],data['seourl'],data['seokeyword'], data['remark'],data['isuse'],data['id']))
        conn.commit()
        conn.close()
        data = {}
        data['status'] = 'success'
        return self.outJson(data)

    def updateIsuerAllRec(self,ids):
        table_name = 'kp_site'
        conn = sqlite3.connect(self.database_name)
        cur = conn.cursor()
        if ids==0:
            cur.execute(f"UPDATE {table_name} SET isuse=0")
        else:
            cur.execute(f"UPDATE {table_name} SET isuse=0 where id IN ({ids})")
        conn.commit()
        conn.close()
        data = {}
        data['status'] = 'success'
        return self.outJson(data)

    def updateWorkstateAllRec(self,ids=0):
        table_name = 'kp_site'
        conn = sqlite3.connect(self.database_name)
        cur = conn.cursor()
        if ids == 0:
            cur.execute(f"UPDATE {table_name} SET workstate=0")
        else:
            cur.execute(f"UPDATE {table_name} SET workstate=0 WHERE id IN ({ids})")
        conn.commit()
        conn.close()
        data = {}
        data['status'] = 'success'
        return self.outJson(data)

    def updateIsuerRec(self,data):
        table_name = 'kp_site'
        conn = sqlite3.connect(self.database_name)
        cur = conn.cursor()
        cur.execute(f"UPDATE {table_name} SET  isuse=?  where id = ?", (data['isuse'],data['id']))
        conn.commit()
        conn.close()
        data = {}
        data['status'] = 'success'
        return self.outJson(data)

    def delete_id(self,data):
        table_name = 'kp_site'
        conn = sqlite3.connect(self.database_name)
        cur = conn.cursor()
        cur.execute(f'DELETE FROM {table_name} where id=?',(data['id'],))
        conn.commit()
        conn.close()
        reqdata = {}
        reqdata['status'] = 'success'
        reqdata['message'] = '成功删除!'
        return self.outJson(reqdata)

    def delete_ids(self, ids):
        table_name = 'kp_site'
        conn = sqlite3.connect(self.database_name)
        cur = conn.cursor()
        cur.execute(f'DELETE FROM {table_name} WHERE id IN ({ids})')
        conn.commit()
        conn.close()
        reqdata = {}
        reqdata['status'] = 'success'
        reqdata['message'] = '成功删除!'
        return self.outJson(reqdata)

    def addRec(self,data):
        table_name = 'kp_site'
        conn = sqlite3.connect(self.database_name)
        cur = conn.cursor()
        now = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        cur.execute(
            f"INSERT INTO {table_name} (seourl,web_name,ranking,seokeyword,clicktotal,create_fmt,lasttime_fmt,isuse,remark) VALUES (?,?,?,?,?,?,?,?,?)",
            (data['seourl'], data['web_name'], 0, data['seokeyword'], 0, now, now,1,data['remark']))
        conn.commit()
        conn.close()
        reqdata = {}
        reqdata['status'] = 'success'
        reqdata['message'] = '新增成功!'
        return self.outJson(reqdata)

    def importfile_data(self):
        table_name = 'kp_site'
        conn = sqlite3.connect(self.database_name)
        cur = conn.cursor()
        file_path = './data/keyword.txt'
        with open(file_path, 'rb') as f:
            content = f.read()
            result = chardet.detect(content)

        with open(file_path, 'r', encoding=result['encoding']) as file:
            for line in file:
                if line.strip():
                    data = line.strip().split('||')
                    data = [item.strip() for item in data]
                    now = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
                    # print(f"正在导入数据：{data}")
                    cur.execute(
                        f"INSERT INTO {table_name} (seourl,web_name,ranking,seokeyword,clicktotal,create_fmt,lasttime_fmt,isuse) VALUES (?,?,?,?,?,?,?,?)",
                        (data[0],data[1],0,data[2],0,now,now,1))

        conn.commit()
        conn.close()
        cursor = cur.execute(f"SELECT * FROM {table_name} order by id asc")
        rows = cursor.fetchall()
        if rows is not None:
            for row in rows:
                print(row)
        print(f"异入{file_path}文件完成...")

    def importLins_data(self,lines):
        table_name = 'kp_site'
        conn = sqlite3.connect(self.database_name)
        cur = conn.cursor()
        if lines.strip():
            data = lines.strip().split('||')
            now = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
            # print(f"正在导入数据：{data}")
            cur.execute(
                f"INSERT INTO {table_name} (seourl,web_name,ranking,seokeyword,clicktotal,create_fmt,lasttime_fmt,isuse) VALUES (?,?,?,?,?,?,?,?)",
                (data[0], data[1], 0, data[2], 0, now, now,1))

        conn.commit()
        conn.close()
        data = {}
        data['status'] = 'success'
        data['data'] = None
        data['message'] = '导入成功'
        return self.outJson(data)

    def click_update_data(self,id,opt='clicktotal'):
        table_name = 'kp_site'
        conn = sqlite3.connect(self.database_name)
        cur = conn.cursor()
        now = datetime.now().strftime('%Y-%m-%d %H:%M:%S')

        try:
            if opt == 'clicktotal':
                cur.execute(
                    f"UPDATE {table_name} SET clicktotal = clicktotal + 1, workstate =1 ,lasttime_fmt = ?  where  id = ?", (now, id))
            else:
                cur.execute(f"UPDATE {table_name} SET successtotal = successtotal + 1 ,lasttime_fmt = ? where  id = ?", (now,id))
            conn.commit()
            conn.close()
        except Exception as e:
            print(e)

    def getList(self):
        table_name = 'kp_site'
        conn = sqlite3.connect(self.database_name)
        cur = conn.cursor()
        sql = f"SELECT * FROM {table_name}  order by lasttime_fmt desc"
        result = self.fetch_data_and_store_as_dict(sql)
        data = {}
        data['status'] = 'fail'
        data['data'] = None
        if result:
            data['status'] = 'success'
            data['data'] = result
        # print("151>>>>>>>>>>>>")
        # print(result)
        return self.outJson(data)

    def getPageList(self, page=1, page_size=5):
        conn = sqlite3.connect(self.database_name)
        cur = conn.cursor()
        table_name = 'kp_site'
        offset = (page - 1) * page_size
        sql = f"SELECT * FROM {table_name}  where isuse=1 and workstate=0 order by id desc "
        cursor = cur.execute(sql)
        columns = [desc[0] for desc in cursor.description]
        rows = cursor.fetchall()
        if not rows:
            return None
        else:
            result = []
            for row in rows:
                row_dict = {}
                for i, column in enumerate(columns):
                    row_dict[column] = row[i]
                result.append(row_dict)

        return result
 

    def getTotal(self):
        conn = sqlite3.connect(self.database_name)
        cur = conn.cursor()
        table_name = 'kp_site'
        sql = f"SELECT count(*) as t_count FROM {table_name}  where isuse=1 and workstate=0"
        cursor = cur.execute(sql)
        row = cursor.fetchone()
        conn.close()
        return row[0]

    def fetch_data_and_store_as_dict(self,sql):
        conn = sqlite3.connect(self.database_name)
        cur = conn.cursor()
        cursor = cur.execute(sql)
        columns = [desc[0] for desc in cursor.description]
        rows = cursor.fetchall()
        if rows:
            result = []
            for row in rows:
                row_dict = {}
                for i, column in enumerate(columns):
                    row_dict[column] = row[i]
                result.append(row_dict)
            return result
        else:
            return None

    def outJson(self,data):
        # 创建一个包含status、code和msg的字典
        response_dict = {
            "status": data['status'],
            "code":data.get('code',0)  ,
            "message": data.get('message',"操作成功!"),
            "requestdata":data.get('data',None)
        }
        # 将字典转换为JSON字符串
        # json_string = json.dumps(response_dict, ensure_ascii=False, indent=2)
        # 输出JSON字符串
        return response_dict



#db = Material()
#db.delete_all_data()
#db.importfile_data()
# db.getlimit(2)

